﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Runtime.Remoting.Metadata.W3cXsd2001;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using ReportSystem_XY.Models;
using ReportSystem_XY.Utility;

namespace ReportSystem_XY.SIP
{
    public class SIPTools
    {

        public static readonly int SIPSterTime = Convert.ToInt32(ConfigurationManager.AppSettings["SIPTime"]);
        /// <summary>
        /// 校准灭菌的开始和结束时间，保证时间差为设定的值
        /// </summary>
        /// <param name="Source"></param>
        /// <param name="SQLTime"></param>
        /// <param name="BatchName"></param>
        public static void CheckSIPStepInfo(string Source, String SQLTime, string BatchName)
        {
            string checkStepString = string.Concat(new string[]
          {
                "Select * From ",
                Source,
                " Where logtime in (Select max(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select min(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset ) AND reportname='",BatchName,
                 "' order by logtime"
          });
            //获取1秒间隔的数据
            DataTable data = new DataTable();
            data = SQLHelper.ExecuteDataTable(checkStepString);

            //获取灭菌步的开始和结束时间
            string sterStart = data.Compute("MIN(logtime)", "batchstep=1 and processStep=4").ToString();
            string sterStop = data.Compute("MAX(logtime)", "batchstep=1 and processStep=4").ToString();
            //计算开始和结束的时间间隔
            int ts = (int)Convert.ToDateTime(sterStop).Subtract(Convert.ToDateTime(sterStart)).TotalSeconds;
            if (ts < SIPSterTime)
            {
                //如果缺，那把开始时间往前挪
                string rightStart = (Convert.ToDateTime(sterStop).AddSeconds(-1 * SIPSterTime)).ToString();
                DataRow[] dr = data.Select("1=1", "logtime asc");
                int span = SIPSterTime - ts;
                for (int j = 0; j < span; j++)
                {
                    string strDate = (Convert.ToDateTime(rightStart).AddSeconds(j)).ToString();
                    for (int i = 0; i < dr.Length; i++)
                    {
                        if ((dr[i]["logtime"].ToString()) == strDate)
                        {
                            // string sqlA = "exec usp_testA '" + strDate + "'," + ToModel.GetRandomNumber(125.0, 130.0, 1).ToString();
                            //SQLHelper.ExecuteNonQuery(sqlA);
                        }
                    }
                }
            }
            else if (ts > SIPSterTime)
            {
                string rightStart = (Convert.ToDateTime(sterStop).AddSeconds(-1 * SIPSterTime)).ToString();
                //string rightEnd = (Convert.ToDateTime(sterStart).AddSeconds(SIPSterTime)).ToString();
                DataRow[] dr = data.Select("1=1", "logtime asc");
                int span = ts - SIPSterTime;
                for (int j = 0; j < span; j++)
                {
                    //string strDate = (Convert.ToDateTime(rightEnd).AddSeconds(j + 1)).ToString();
                    string strDate = (Convert.ToDateTime(rightStart).AddSeconds(j)).ToString();
                    for (int i = 0; i < dr.Length; i++)
                    {
                        if ((dr[i]["logtime"].ToString()) == strDate)
                        {
                            //string sqlA = "exec usp_testB '" + strDate + "'" + ToModel.GetRandomNumber(125.0, 130.0, 1).ToString();
                            //SQLHelper.ExecuteNonQuery(sqlA);
                        }
                    }
                }
            }
        }
        /// <summary>
        /// 获取步骤的时间信息
        /// </summary>
        /// <param name="Source"></param>
        /// <param name="SQLTime"></param>
        /// <param name="BatchName"></param>
        /// <returns></returns>
        public static DataTable GetSIPStepInfo(string Source, String SQLTime, string BatchName)
        {
            //组装查询步序开始结束时间信息的SQL语句
            string CmdStepInfo = string.Concat(new string[]
             {
                "SELECT DISTINCT T1.processStep,T1.batchstep,MIN(T1.StartTime)AS StartTime,MIN(T1.StopTime) AS StopTime FROM" +
                "(select [batchstep],[processStep],[logtime] as StartTime,StopTime from",
                " (select  batchstep, processStep,logtime,  lead(logtime,1,logtime)Over(order by logtime)StopTime From ",
                Source,
                " Where logtime in (Select max(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select min(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset ) AND reportname='",BatchName,
                 "')  as T ) AS T1 WHERE T1.batchstep <=1 GROUP BY processStep,batchStep" // order by StartTime"
                 +
                " union (select processStep=0, batchstep,min(logtime) as StartTime,max(logtime) as StopTime from ",
                 Source,
                " where reportname='",BatchName,
               "' and batchstep=2 group by batchstep)"+

" union (select processStep=0, batchstep,min(logtime) as StartTime,max(logtime) as StopTime from ",Source,
" where reportname='",BatchName,
"' and batchstep=3 group by batchstep) order by StartTime"
            });

            return SQLHelper.ExecuteDataTable(CmdStepInfo);
        }

        public static DataTable GetCIPStepInfo(string Source, String SQLTime, string BatchName)
        {
            //组装查询步序开始结束时间信息的SQL语句
            string CmdStepInfo = string.Concat(new string[]
             {
                "SELECT T1.batchstep,MIN(T1.StartTime)AS StartTime,MAX(T1.StartTime) AS StopTime FROM" +
                "(select [batchstep],[processStep],[logtime] as StartTime,StopTime from",
                " (select  batchstep, processStep,logtime,  lead(logtime,1,logtime)Over(order by logtime)StopTime From ",
                Source,
                " Where logtime in (Select max(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select min(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset ) AND reportname='",BatchName,
                 "')  as T ) AS T1 GROUP BY batchStep order by StartTime"
            });

            return SQLHelper.ExecuteDataTable(CmdStepInfo);
        }
        public static DataTable GetCIPStepInfos(string Source, string BatchName)
        {
            //组装查询步序开始结束时间信息的SQL语句
            string CmdStepInfo = string.Concat(new string[]
            {
                 "SELECT  batchStep, MIN(logtime) as StartTime,MAX(logtime) as StopTime FROM ",Source,
                 " WHERE reportname='",BatchName,
                 "' GROUP BY batchStep ORDER BY StartTime"
                //" WHERE reportname='",BatchName,"' and batchStep>=1 and batchStep<=3 GROUP BY batchStep
                //"SELECT * FROM "+
                //"(SELECT  batchStep, processStep=-1,MIN(logtime) as StartTime,MAX(logtime) as StopTime FROM ",Source,
                //" WHERE reportname='",BatchName,"' and batchStep>=1 and batchStep<=3 GROUP BY batchStep union "+
                //"SELECT  batchStep=4, processStep,MIN(logtime) as StartTime,MAX(logtime) as StopTime FROM ",
                //Source,
                //" WHERE reportname='",BatchName,"' and batchStep=1 GROUP BY processStep union "+
                //"SELECT  batchStep, processStep=-1,MIN(logtime) as StartTime,MAX(logtime) as StopTime FROM ",Source,
                //" WHERE reportname='",BatchName,"' and batchStep=5 GROUP BY batchStep) as t ORDER BY StartTime,processStep"
            });

            return SQLHelper.ExecuteDataTable(CmdStepInfo);
        }

        public static string GetCmdTextString(string Source, string SQLTime, string SampleTime, string BatchName)
        {
            return string.Concat(new string[]
            {
                "Select * From ",
                Source,
                " Where logtime in (Select logtime From ",
                Source,
                SQLTime,
                "And (DATEDIFF(s, convert(nvarchar(20), logtime, 120), convert(nvarchar(20), GETDATE(), 120)) %",
                SampleTime,
                "= 0)union Select max(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select min(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset ) AND reportname='",BatchName,
                 "'  order by logtime"
            });
        }
        public static string GetCmdTextString(string Source,string SampleTime, string BatchName)
        {
            string batchNameFilter = GetBatchNameFilter(BatchName);
            return string.Concat(new string[]
            {
                "Select * From ",
                Source,
                " Where logtime in (Select logtime From ",
                Source,
                batchNameFilter,
                "And (DATEDIFF(s, convert(nvarchar(20), logtime, 120), convert(nvarchar(20), GETDATE(), 120)) %",
                SampleTime,
                "= 0)union Select max(logtime)logtime From ",
                Source,
                batchNameFilter,
                "group by processStep union Select min(logtime)logtime From ",
                Source,
                batchNameFilter,
                "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                batchNameFilter,
                ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                batchNameFilter,
                ")_ Where processStep<> Offset ) order by logtime"
            });
        }
        public static string GetCmdTextString(string Source, string SQLTime, string SampleTime, string BatchName,int tankid)
        {
            if(tankid==1)
            {
                return string.Concat(new string[]
                {
                    "Select logtime,batchstep,processstep,TT2001,TT2002,TT2050,TT3003,TT3004 From ",
                    Source,
                    " Where logtime in (Select logtime From ",
                    Source,
                    SQLTime,
                    "And (DATEDIFF(s, convert(nvarchar(20), logtime, 120), convert(nvarchar(20), GETDATE(), 120)) %",
                    SampleTime,
                    "= 0)union Select max(logtime)logtime From ",
                    Source,
                    SQLTime,
                    "group by processStep union Select min(logtime)logtime From ",
                    Source,
                    SQLTime,
                    "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                    Source,
                    SQLTime,
                    ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                    Source,
                    SQLTime,
                    ")_ Where processStep<> Offset ) AND reportname='",BatchName,
                    "' order by logtime"
                });
            }
            else if (tankid == 2)
            {
                return string.Concat(new string[]
                {
                    "Select logtime,batchstep,processstep,TT2101,TT2102,TT2150,TT3106,TT3004 From ",
                    Source,
                    " Where logtime in (Select logtime From ",
                    Source,
                    SQLTime,
                    "And (DATEDIFF(s, convert(nvarchar(20), logtime, 120), convert(nvarchar(20), GETDATE(), 120)) %",
                    SampleTime,
                    "= 0)union Select max(logtime)logtime From ",
                    Source,
                    SQLTime,
                    "group by processStep union Select min(logtime)logtime From ",
                    Source,
                    SQLTime,
                    "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                    Source,
                    SQLTime,
                    ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                    Source,
                    SQLTime,
                    ")_ Where processStep<> Offset ) AND reportname='",BatchName,
                    "' order by logtime"
                });
            }
            else if (tankid == 3)
            {
                return string.Concat(new string[]
                {
                    "Select logtime,batchstep,processstep,TT3004,TT2104,TT3101,TT3102,TT3103,TT3104,TT3105,TT3107 From ",
                    Source,
                    " Where logtime in (Select logtime From ",
                    Source,
                    SQLTime,
                    "And (DATEDIFF(s, convert(nvarchar(20), logtime, 120), convert(nvarchar(20), GETDATE(), 120)) %",
                    SampleTime,
                    "= 0)union Select max(logtime)logtime From ",
                    Source,
                    SQLTime,
                    "group by processStep union Select min(logtime)logtime From ",
                    Source,
                    SQLTime,
                    "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                    Source,
                    SQLTime,
                    ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                    Source,
                    SQLTime,
                    ")_ Where processStep<> Offset ) AND reportname='",BatchName,
                    "' order by logtime"
                });
            }
            else
            {
                return null;
            }                         
        }
        public static DataTable GetSIPStepInfos(string Source, string BatchName)
        {
            string sql = string.Concat(new string[]
             {
                "SELECT * FROM "+
                "(SELECT  batchStep=1, processStep,MIN(logtime) as StartTime,MAX(logtime) as StopTime FROM ",
                Source,
                " WHERE reportname='",BatchName,"' and batchStep=1 GROUP BY processStep union "+
                "SELECT  batchStep, processStep=-1,MIN(logtime) as StartTime,MAX(logtime) as StopTime FROM ",Source,
                " WHERE reportname='",BatchName,"' and batchStep>1 and batchStep<=3 GROUP BY batchStep) as t ORDER BY StartTime,processStep"
             });
            return SQLHelper.ExecuteDataTable(sql);
        }
       

        public static void CalSIPStep(DataTable dt)
        {
            //步序时间的调整
            DataRow[] drStep = dt.Select("1=1", "StartTime asc");
            for (int i = 1; i < drStep.Length; i++)
            {
                string a = drStep[i][2].ToString();
                string b = drStep[i - 1][3].ToString();


                int ii = ((int)Convert.ToDateTime(drStep[i][2].ToString()).Subtract(Convert.ToDateTime(drStep[i - 1][3].ToString())).TotalSeconds);
                if (ii > 1 & (int)drStep[i][0] == 1 & (int)drStep[i][1] == 4)
                {
                    string newDate = (Convert.ToDateTime(a).AddSeconds(-1)).ToString("yyyy-MM-dd HH:mm:ss");
                    string filteDate = Convert.ToDateTime(b).ToString("yyyy-MM-dd HH:mm:ss");
                    string sqlA = "exec usp_testD '" + newDate + "','" + filteDate + "'";
                    SQLHelper.ExecuteNonQuery(sqlA);
                    Thread.Sleep(200);

                }

                if ((ii > 1 & (int)drStep[i][0] == 1 & (int)drStep[i][1] < 4) || (ii > 1 & (int)drStep[i][0] > 1))
                {
                    string newDate = (Convert.ToDateTime(b).AddSeconds(1)).ToString("yyyy-MM-dd HH:mm:ss");
                    string filteDate = Convert.ToDateTime(a).ToString("yyyy-MM-dd HH:mm:ss");
                    string sqlA = "exec usp_testD '" + newDate + "','" + filteDate + "'";
                    SQLHelper.ExecuteNonQuery(sqlA);
                }
            }
        }
        public static void CalCIPStep(DataTable dt)
        {
            DataRow[] drStep = dt.Select("1=1", "StartTime asc");
            for (int i = 1; i < drStep.Length; i++)
            {
                string a = drStep[i][1].ToString();
                string b = drStep[i - 1][2].ToString();


                int ii = ((int)Convert.ToDateTime(drStep[i][1].ToString()).Subtract(Convert.ToDateTime(drStep[i - 1][2].ToString())).TotalSeconds);
                if (ii > 1)
                {
                    string newDate = (Convert.ToDateTime(b).AddSeconds(1)).ToString("yyyy-MM-dd HH:mm:ss");
                    string filteDate = Convert.ToDateTime(a).ToString("yyyy-MM-dd HH:mm:ss");
                    string sqlA = "exec usp_testE '" + newDate + "','" + filteDate + "'";
                    SQLHelper.ExecuteNonQuery(sqlA);
                }
            }
        }
        public static string GetBatchNameFilter(string batchname)
        {
            return string.Concat(new string[]
            {
                " where reportname = '",batchname,
                "' "
            });
        }
        public static void ProductionPhaseTimeCal(string reportname)
        {
            for (int i = 1; i < 15; i++)
            {
                string sql = "select * FROM tblP" + i.ToString() + " WHERE reportname = '" + reportname + "'";
                DataTable dt = new DataTable();
                dt = SQLHelper.ExecuteDataTable(sql);
                DataRow[] dr = dt.Select("1=1", "starttime asc");
                if(dr.Length>1)
                {           
                for (int j = 0; j < dr.Length - 1; j++)
                {
                    if (Convert.ToDateTime(dr[j][4].ToString()).ToString("yyyy-MM-dd HH:mm:ss") != Convert.ToDateTime(dr[j+1][3].ToString()).ToString("yyyy-MM-dd HH:mm:ss"))
                    {
                        string sqlA = "exec usp_p" + i.ToString() + " '" + dr[j + 1][3] + "','"+ reportname + "','" + dr[j][3] +"'";
                            SQLHelper.ExecuteNonQuery(sqlA);
                    }

                }
                    Thread.Sleep(500);
                }
                
            }
        }
        public static DataTable GetProductionInfo(string reportname)
        {
            string sql = "SELECT * FROM tblP1 WHERE reportname = '" + reportname + "' ";

            for (int i = 2; i <= 14; i++)
            {
                sql = sql + "UNION SELECT * FROM tblP" + i.ToString() + " WHERE reportname = '" + reportname + "' ";
            }
            return SQLHelper.ExecuteDataTable(sql);
        }
        public static string GetCmdTextString(string Source, string SampleTime, string BatchName, string start, string stop)
        {
            string batchNameFilter = string.Concat(new string[]
            {
                " where reportname = '",BatchName,
                "' AND logtime between '",start,"' and '",stop,"'"
            });
            return string.Concat(new string[]
            {
                "Select * From ",
                Source,
                batchNameFilter,
                "And DATEDIFF(s, convert(nvarchar(20),'"+start+"', 120), convert(nvarchar(20), logtime, 120)) %'"+SampleTime+"'=0 order by logtime"
                //SampleTime,
               // "= 0 order by logtime "
            });
        }
    }
}
